
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 12/18/2013 15:17:06
-- Generated from EDMX file: C:\Users\Du\documents\visual studio 2010\Projects\CmuCertificates\Database\CMUCertificateModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [CMUCertificateManager];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_Certificate_Course]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Certificate] DROP CONSTRAINT [FK_Certificate_Course];
GO
IF OBJECT_ID(N'[dbo].[FK_Certificate_Student]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Certificate] DROP CONSTRAINT [FK_Certificate_Student];
GO
IF OBJECT_ID(N'[dbo].[FK_Course_Lecturer]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Course] DROP CONSTRAINT [FK_Course_Lecturer];
GO
IF OBJECT_ID(N'[dbo].[FK_Course_Year]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Course] DROP CONSTRAINT [FK_Course_Year];
GO
IF OBJECT_ID(N'[dbo].[FK_Student_Class]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Student] DROP CONSTRAINT [FK_Student_Class];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Certificate]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Certificate];
GO
IF OBJECT_ID(N'[dbo].[Class]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Class];
GO
IF OBJECT_ID(N'[dbo].[Course]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Course];
GO
IF OBJECT_ID(N'[dbo].[Lecturer]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Lecturer];
GO
IF OBJECT_ID(N'[dbo].[Student]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Student];
GO
IF OBJECT_ID(N'[dbo].[Year]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Year];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Certificates'
CREATE TABLE [dbo].[Certificates] (
    [CertificateID] nvarchar(max)  NOT NULL,
    [StudentID] varchar(50)  NOT NULL,
    [CourseID] nvarchar(max)  NOT NULL,
    [Score] varchar(50)  NOT NULL
);
GO

-- Creating table 'Classes'
CREATE TABLE [dbo].[Classes] (
    [ClassID] nvarchar(max)  NOT NULL,
    [ClassName] nvarchar(50)  NOT NULL,
    [ClassYear] varchar(50)  NOT NULL
);
GO

-- Creating table 'Courses'
CREATE TABLE [dbo].[Courses] (
    [CourseID] nvarchar(max)  NOT NULL,
    [CourseName] nvarchar(50)  NOT NULL,
    [CourseShortName] nvarchar(50)  NOT NULL,
    [YearID] nvarchar(max)  NOT NULL,
    [LecturerID] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Lecturers'
CREATE TABLE [dbo].[Lecturers] (
    [LecturerID] nvarchar(max)  NOT NULL,
    [LecturerName] nvarchar(50)  NOT NULL
);
GO

-- Creating table 'Students'
CREATE TABLE [dbo].[Students] (
    [StudentID] varchar(50)  NOT NULL,
    [FirstName] nvarchar(50)  NOT NULL,
    [MiddleName] nvarchar(50)  NOT NULL,
    [LastName] nvarchar(50)  NOT NULL,
    [Gender] bit  NOT NULL,
    [Birthday] datetime  NOT NULL,
    [ClassID] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Years'
CREATE TABLE [dbo].[Years] (
    [YearID] nvarchar(max)  NOT NULL,
    [Year1] varchar(50)  NOT NULL,
    [Semester] varchar(50)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [CertificateID] in table 'Certificates'
ALTER TABLE [dbo].[Certificates]
ADD CONSTRAINT [PK_Certificates]
    PRIMARY KEY CLUSTERED ([CertificateID] ASC);
GO

-- Creating primary key on [ClassID] in table 'Classes'
ALTER TABLE [dbo].[Classes]
ADD CONSTRAINT [PK_Classes]
    PRIMARY KEY CLUSTERED ([ClassID] ASC);
GO

-- Creating primary key on [CourseID] in table 'Courses'
ALTER TABLE [dbo].[Courses]
ADD CONSTRAINT [PK_Courses]
    PRIMARY KEY CLUSTERED ([CourseID] ASC);
GO

-- Creating primary key on [LecturerID] in table 'Lecturers'
ALTER TABLE [dbo].[Lecturers]
ADD CONSTRAINT [PK_Lecturers]
    PRIMARY KEY CLUSTERED ([LecturerID] ASC);
GO

-- Creating primary key on [StudentID] in table 'Students'
ALTER TABLE [dbo].[Students]
ADD CONSTRAINT [PK_Students]
    PRIMARY KEY CLUSTERED ([StudentID] ASC);
GO

-- Creating primary key on [YearID] in table 'Years'
ALTER TABLE [dbo].[Years]
ADD CONSTRAINT [PK_Years]
    PRIMARY KEY CLUSTERED ([YearID] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [CourseID] in table 'Certificates'
ALTER TABLE [dbo].[Certificates]
ADD CONSTRAINT [FK_Certificate_Course]
    FOREIGN KEY ([CourseID])
    REFERENCES [dbo].[Courses]
        ([CourseID])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Certificate_Course'
CREATE INDEX [IX_FK_Certificate_Course]
ON [dbo].[Certificates]
    ([CourseID]);
GO

-- Creating foreign key on [StudentID] in table 'Certificates'
ALTER TABLE [dbo].[Certificates]
ADD CONSTRAINT [FK_Certificate_Student]
    FOREIGN KEY ([StudentID])
    REFERENCES [dbo].[Students]
        ([StudentID])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Certificate_Student'
CREATE INDEX [IX_FK_Certificate_Student]
ON [dbo].[Certificates]
    ([StudentID]);
GO

-- Creating foreign key on [ClassID] in table 'Students'
ALTER TABLE [dbo].[Students]
ADD CONSTRAINT [FK_Student_Class]
    FOREIGN KEY ([ClassID])
    REFERENCES [dbo].[Classes]
        ([ClassID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Student_Class'
CREATE INDEX [IX_FK_Student_Class]
ON [dbo].[Students]
    ([ClassID]);
GO

-- Creating foreign key on [LecturerID] in table 'Courses'
ALTER TABLE [dbo].[Courses]
ADD CONSTRAINT [FK_Course_Lecturer]
    FOREIGN KEY ([LecturerID])
    REFERENCES [dbo].[Lecturers]
        ([LecturerID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Course_Lecturer'
CREATE INDEX [IX_FK_Course_Lecturer]
ON [dbo].[Courses]
    ([LecturerID]);
GO

-- Creating foreign key on [YearID] in table 'Courses'
ALTER TABLE [dbo].[Courses]
ADD CONSTRAINT [FK_Course_Year]
    FOREIGN KEY ([YearID])
    REFERENCES [dbo].[Years]
        ([YearID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Course_Year'
CREATE INDEX [IX_FK_Course_Year]
ON [dbo].[Courses]
    ([YearID]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------